package com.portalEsporteClubeBahia.app.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.portalEsporteClubeBahia.app.ApplicationException;
import com.portalEsporteClubeBahia.app.vo.UserVO;

public class UserDAO extends DAOFactory {

	public UserDAO() throws ApplicationException {
		super();
	}
	
	public UserVO findByUserPassword(UserVO user) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT * FROM TBL_USER WHERE USER = ? AND PASSWORD = ?");
		
		UserVO answer = new UserVO();
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, user.getUser());
			ps.setString(2, user.getPassword());
			rs = ps.executeQuery();
			
			if(rs.next()){
				answer.setId(rs.getInt("ID"));
				answer.setName(rs.getString("NAME"));
				answer.setUser(rs.getString("USER"));
				answer.setPassword(rs.getString("PASSWORD"));
				answer.setAdm(rs.getBoolean("ADM"));
			}else{
				throw new ApplicationException("User not found!");
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return answer;
		
	}
	
	public UserVO findByUser(UserVO user) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT * FROM TBL_USER WHERE USER = ?");
		
		UserVO answer = new UserVO();
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, user.getUser());
			rs = ps.executeQuery();
			
			if(rs.next()){
				answer.setId(rs.getInt("ID"));
				answer.setName(rs.getString("NAME"));
				answer.setUser(rs.getString("USER"));
				answer.setPassword(rs.getString("PASSWORD"));
			}else{
				answer = null;
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return answer;
		
	}
	
	public List<UserVO> findByFilter(UserVO user) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT * FROM TBL_USER WHERE 1 = 1 ");
		if(user.getUser()!=null && !user.getUser().equalsIgnoreCase("")){
			sql.append(" AND USER LIKE '%"+user.getUser()+"%'");
		}
		if(user.getName()!=null && !user.getName().equalsIgnoreCase("")){
			sql.append(" AND NAME LIKE '%"+user.getName()+"%'");
		}
		
		List<UserVO> list = new ArrayList<UserVO>();
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				UserVO answer = new UserVO();
				answer.setId(rs.getInt("ID"));
				answer.setName(rs.getString("NAME"));
				answer.setUser(rs.getString("USER"));
				answer.setPassword(rs.getString("PASSWORD"));
				list.add(answer);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
		
	}
	
	public void insert(UserVO user)throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("INSERT INTO TBL_USER (NAME, USER, PASSWORD) VALUES (?,?,?)");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, user.getName());
			ps.setString(2, user.getUser());
			ps.setString(3, user.getPassword());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void update(UserVO user)throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("UPDATE TBL_USER SET NAME = ?, USER = ?, PASSWORD = ? WHERE ID = ?");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, user.getName());
			ps.setString(2, user.getUser());
			ps.setString(3, user.getPassword());
			ps.setInt(4, user.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void updatePassword(UserVO user)throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("UPDATE TBL_USER SET PASSWORD = ? WHERE ID = ?");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, user.getPassword());
			ps.setInt(2, user.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void delete(UserVO user)throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("DELETE FROM TBL_USER WHERE ID = ?");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, user.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public List<UserVO> findAll() throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT * FROM TBL_USER ORDER BY NAME");
		
		List<UserVO> list = new ArrayList<UserVO>();
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			if(rs.next()){
				UserVO answer = new UserVO();
				answer.setId(rs.getInt("ID"));
				answer.setName(rs.getString("NAME"));
				answer.setUser(rs.getString("USER"));
				answer.setPassword(rs.getString("PASSWORD"));
				list.add(answer);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
		
	}
	
	public UserVO findByPrimaryKey(UserVO user) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT * FROM TBL_USER WHERE ID = ?");
		
		UserVO answer = new UserVO();
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, user.getId());
			rs = ps.executeQuery();
			
			if(rs.next()){
				answer.setId(rs.getInt("ID"));
				answer.setName(rs.getString("NAME"));
				answer.setUser(rs.getString("USER"));
				answer.setPassword(rs.getString("PASSWORD"));
			}else{
				throw new ApplicationException("User not found!");
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return answer;
		
	}

}
